package org.medicalinstrument.impl;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.medicalinstrument.bean.PageBean;
import org.medicalinstrument.bean.Product;
import org.medicalinstrument.dao.ProductDao;
import org.medicalinstrument.database.DataSourceUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    private QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());


    @Override
    public int totalCount(String conType, String conName) throws SQLException {
        String sql = "select count(*) from product where 1 = 1 ";
        StringBuilder sb = new StringBuilder(sql);
        List<Object> params = new ArrayList<Object>();
        if (conType != null && !"".equals(conType)){
            sb.append(" and productType like ? ");
            params.add("%"+conType+"%");
        }
        if (conName != null && !"".equals(conName)){
            sb.append(" and productName like ? ");
            params.add("%"+conName+"%");
        }
        //sb.append(" and isBroken = '0' ");
        Long query = runner.query(sb.toString(), new ScalarHandler<>(),params.toArray());
        return query.intValue();
    }

    /*报废商品总数*/
    @Override
    public int brokenCount() throws SQLException {
        String sql= "select count(*) from product where isBroken = '1'";
        Long query = runner.query(sql, new ScalarHandler<>());
        return query.intValue();
    }

    public int getQR(String accountNo) throws SQLException {
        String sql = "select * from productCart_view  where accountNo='"+accountNo+"'";

//        String sql = "select productName from product  where productQR="+productQR;
        //System.out.println(sql);
        String productQR = runner.query(sql, new ScalarHandler<String>(3));
        System.out.println("QR:  "+productQR);
        return getProductNumber(productQR);
    }

    public int getProductNumber(String productQR) throws SQLException {
        //System.out.println(productQR);
        String sql = "select * from product  where productQR="+productQR;
//        String sql = "select productName from product  where productQR="+productQR;
        // System.out.println(sql);
        String productName ,productType ;
        productName = runner.query(sql, new ScalarHandler<String>(3));
        sql = "select * from product where productQR="+productQR;
        productType = runner.query(sql, new ScalarHandler<String>(2));
        sql = "select count(*) from product where productName='"+productName+"' and productType = '"+productType+"'";
        List<Product> list = runner.query(sql,new BeanListHandler<>(Product.class));
       // Object number = runner.query(sql, new ScalarHandler<>());
        //Integer y = Integer.parseInt(number.toString());
       // System.out.println("number:"+ list.size());
        return list.size();

    }
    //根据二维码删除商品
    @Override
    public boolean deleteByProductQR(String ProductQR) throws SQLException {
        String sql = "delete from product where productQR = ?";
        int update = runner.update(sql,ProductQR);
        if(update > 0){
            return true;
        }
        else{
            return false;
        }
    }

    //根据二维码修改商品
    @Override
    public boolean updateByProductQR(Product product) throws SQLException {
        System.out.println("dao..."+product.getIsBroken());
        String sql ="update product set saleableInformation = ?,introduction = ?,salePrice = ?,rentPrice = ?,isBroken = ?,rentMoney = ? " +
                "where productQR = ?";
        int update = runner.update(sql,
                product.getSaleableInformation(),product.getIntroduction(),product.getSalePrice(), product.getRentPrice(), product.getIsBroken(),
                product.getRentMoney(),product.getProductQR());
        if(update > 0){

            return true;
        }
        else{
            return false;
        }
    }

    //根据二维码查询商品
    @Override
    public Product getByProductQR(String ProductQR) throws SQLException {
        String sql = "select * from product where productQR = ?";
        Product product = runner.query(sql, new BeanHandler<Product>(Product.class),ProductQR);
        product.setProductNumber(getProductNumber(ProductQR));
        return product;
    }

  /*  public int getProductCount(String productQR) throws SQLException {
        String sql = "select * from product where productQR = "+productQR;
       String productName = runner.query(sql, new ScalarHandler<String>(3));
       String productType = runner.query(sql, new ScalarHandler<String>(2));
       sql = "select count(*) from product where productType='" + productType+ "' and productName='"+ productName +"'";
        System.out.println(sql);
       Long count  = runner.query(sql, new ScalarHandler<Long>());
        System.out.println("count : "+ count);
       return Integer.parseInt(count.toString());
    }*/

    //添加商品
    @Override
    public boolean addProduct(Product product) throws SQLException {
        String sql = "insert into product (productQR,productType,productName,productColor,productFactory," +
                "productDate,productPhoto,saleableInformation,productNumber,salePrice,rentPrice," +
                "isBroken,rentMoney,state,introduction) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int add = runner.update(sql,product.getProductQR(),product.getProductType(),product.getProductName(),product.getProductColor(),
                product.getProductFactory(),product.getProductDate(),product.getProductPhoto(),product.getSaleableInformation(),product.getProductNumber(),
                product.getSalePrice(),product.getRentPrice(),product.getIsBroken(),product.getRentMoney(),product.getState(),product.getIntroduction());
        if(add > 0){
            return true;
        }
        else{
            return false;
        }
    }

    @Override
    public List<Product> showByPage(PageBean page, String conType, String conName) throws SQLException {
        String sql = "select * from product where 1 = 1 ";
        int start = (page.getCurrentPage() - 1) * page.getRows();
        StringBuilder sb = new StringBuilder(sql);
        List<Object> params = new ArrayList<Object>();
        if (conType != null && !"".equals(conType)){
            sb.append(" and productType like ? ");
            params.add("%"+conType+"%");
        }
        if (conName != null && !"".equals(conName)){
            sb.append(" and productName like ? ");
            params.add("%"+conName+"%");
        }
        //sb.append(" and isBroken = '0' and state=0");
        sb.append(" limit ?,? ");
        params.add(start);
        params.add(page.getRows());
        List<Product> list = runner.query(sb.toString(), new BeanListHandler<Product>(Product.class), params.toArray());
        return list;
    }
    @Override
    public List<Product> showByPage1(PageBean page, String conType, String conName) throws SQLException {
        String sql = "select * from product where 1 = 1 ";
        int start = (page.getCurrentPage() - 1) * page.getRows();
        StringBuilder sb = new StringBuilder(sql);
        List<Object> params = new ArrayList<Object>();
        if (conType != null && !"".equals(conType)){
            sb.append(" and productType like ? ");
            params.add("%"+conType+"%");
        }
        if (conName != null && !"".equals(conName)){
            sb.append(" and productName like ? ");
            params.add("%"+conName+"%");
        }
        sb.append(" and isBroken = '0' and state=0");
        sb.append(" limit ?,? ");
        params.add(start);
        params.add(page.getRows());
        List<Product> list = runner.query(sb.toString(), new BeanListHandler<Product>(Product.class), params.toArray());
        return list;
    }


    public List<Product> showByPage2(PageBean page, String conType, String conName) throws SQLException {
        String sql = "select * from product where 1 = 1 ";
        int start = (page.getCurrentPage() - 1) * page.getRows();
        StringBuilder sb = new StringBuilder(sql);
        List<Object> params = new ArrayList<Object>();
        if (conType != null && !"".equals(conType)){
            sb.append(" and productType like ? ");
            params.add("%"+conType+"%");
        }
        if (conName != null && !"".equals(conName)){
            sb.append(" and productName like ? ");
            params.add("%"+conName+"%");
        }
        sb.append(" and isBroken = '0' and state=0");
        sb.append(" limit ?,? ");
        params.add(start);
        params.add(page.getRows());
        List<Product> list = runner.query(sb.toString(), new BeanListHandler<Product>(Product.class), params.toArray());
        return list;
    }
    //根据二维码修改商品
    @Override
    public boolean updateState(Connection conn, int state, String ProductQR) throws SQLException {
        String sql ="update product set state = ? where productQR = ?";
        int update = runner.update(conn,sql, state,ProductQR);
        if(update > 0){
            return true;
        }
        else{
            return false;
        }
    }

    public boolean updateState(int state, String productQR , int buyCount) throws SQLException {
        String productName ,productType ;
        int update = 0 ;
        String sql = "select * from product where productQR=" + productQR;
        productName = runner.query(sql, new ScalarHandler<String>(3));
        sql = "select * from product where productQR="+productQR;
        productType = runner.query(sql, new ScalarHandler<String>(2));

        sql =" update product set state = 2 where productQR in " +
                "(select t.productQR  from (select * from product where productName = ? and productType = ? limit 1)as t)";
        for (int i = 0 ; i < buyCount ; i ++){
            update +=  runner.update(sql,productName,productType);
        }
        return update > 0;
    }

    @Override
    public List<Product> getAllProduct() throws SQLException {
        //QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select * from product where isBroken='0' and state=0";
        List<Product> list = runner.query(sql,new BeanListHandler<>(Product.class));
        //System.out.println(list.size());
        /*for(int i = 0 ; i < list.size() ; i ++ ){
            //System.out.println(getProductNumber(list.get(i).getProductQR()));
            //list.get(i).setProductNumber((int)getProductNumber(list.get(i).getProductQR()));
           System.out.println(list.get(i));
        }*/
        //System.out.println("dao----" + list);
        return list;
    }
}
